# -*- coding: utf-8 -*-
"""
@Time ： 2021/3/19 0:37
@Auth ： 买菠萝凤梨
@File ：sql_excute.py
@IDE ：PyCharm
@Motto：work steadily

"""
import pymysql
from public.log import Log
from tools.read_config import Read_conf

log = Log()
read_ini = Read_conf().load_ini('test.ini')


class Sql_DB:
    # 初始化sql连接
    def __init__(self, db):
        # 读取文件的数据
        host = read_ini[db]["host"]
        port = int(read_ini[db]["port"])
        user = read_ini[db]["user"]
        password = read_ini[db]["password"]
        database = read_ini[db]["db"]
        charset = read_ini[db]["charset"]
        try:
            self.conn = pymysql.connect(host=host, port=port, user=user,
                                        password=password, database=database, charset=charset)
            log.info('数据库连接成功')
            # 通过 cursor() 创建游标对象，并让查询结果以字典格式输出
            self.cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        except Exception as e:
            log.error('初始化数据库连接失败：{}'.format(e))

    def __del__(self):
        # 关闭游标
        self.cur.close()
        # 关闭数据连接
        self.conn.close()

    def select(self, sql):
        '''sql查询语句'''
        try:
            self.conn.ping(reconnect=True)
            self.cur.execute(sql)
            data = self.cur.fetchall()
            log.info('{}执行成功'.format(sql))
            return data
        except Exception as e:
            log.error('查询失败{}'.format(e))

    def excute_sql(self, sql):
        '''执行updata/insert/delete语句'''
        try:
            self.conn.ping(reconnect=True)
            self.cur.execute(sql)
            log.info('{}执行成功'.format(sql))
            self.conn.commit()
        except Exception as e:
            log.error('sql执行失败：{}，事务回滚'.format(e))
            self.conn.rollback()

if __name__ == '__main__':
    sql = Sql_DB('PRODB')
    data = sql.select('select * from testdata')
    print(data)
